# 获取一个网格图层与一个部件图层的包含统计关系
contains_sql = '''
    WITH _containsInfo AS (
        SELECT
            b."{10}",
            COUNT (1) AS num 
        FROM
            "{0}" A, -- 部件名称
            "{1}" b -- 网格名称
        WHERE
            st_contains ( b.geom, A.geom ) 
        GROUP BY
            b."{10}"
        ) insert into tc_spatial_contain_stat
        SELECT
        region_code,
        region_name,
        '{2}' as region_type_name, -- 网格的图层用途名称
        '{3}' as usage_id, -- 网格的图层用途号
        '{4}' as sub_unique_code, -- 部件所属小类编码
        '{5}' as sub_type_name, -- 部件所属小类名称
        '{6}' as main_unique_code, -- 部件所属大类编码
        '{7}' as main_type_name, -- 部件所属大类名称
        info.num as nums, -- 包含部件数量
        region_id, -- 网格对象对应tc_region中的region_id
        0 AS part_area, -- 部件面积 0
        {8} as phy_layer_id, -- 部件图层号
        1 as geom_type -- 部件几何类型         
    FROM
        tc_region region
        LEFT JOIN _containsInfo info ON region.region_code = info.{10} 
    WHERE
        region.region_type = {9} 
        and info.num is not null ;
        '''

# 返回与tc_spatial_contain_stat_tree表接口相似的结果
tree_sql = '''
    SELECT
        a.phy_layer_id,
        c.phy_layer_tname,
        b.unique_code,
        -- a.type_id,
        b.type_name,
        d.unique_code as main_unique_code,
        d.type_name AS main_type_name ,
        1 as geom_type
    FROM
        tc_event_subtype_phy_layer a
        LEFT JOIN tc_dic_event_any_type b ON a.type_id = b.type_id
        LEFT JOIN tc_phy_layer c ON c.phy_layer_id = a.phy_layer_id
        LEFT JOIN tc_dic_event_any_type d ON d.type_id = b.senior_id
        '''

get_event_any_type_sql = "select type_id, type_name, unique_code from tc_dic_event_any_type WHERE type_level=3"

